package org.edu.eece262.taskshare;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.util.Log;

public class TSDBAdapter {
	
	// Let's be a Singleton
	private static TSDBAdapter instance = null;

	// Member Variables
	private static final String DB_NAME = "TaskShare.db";
	private static final String DB_TABLE = "LocalTasks";
	private static final int DB_VERSION = 5;

	public static final String DB_KEY_ID = "_id";

	// Public Key (Column) Names for convenience
	public static final String KEY_GLOBAL_ID = "GlobalID";
	public static final String KEY_NAME = "Name";
	public static final String KEY_NOTES = "Notes";
	public static final String KEY_LOCATION = "Location";
	public static final String KEY_PARENT_LOCAL = "ParentLocal";
	public static final String KEY_PARENT_GLOBAL = "ParentGlobal";
	public static final String KEY_DUE_DATE = "DueDate";
	public static final String KEY_DUE_TIME = "DueTime";
	public static final String KEY_PERCENT_COMPLETE = "PercentComplete";
	public static final String KEY_DONE = "Done";
	public static final String KEY_PRIORITY = "Priority";

	// Public Column numbers for convenience
	public static final int GLOBAL_ID_COLUMN = 1;
	public static final int NAME_COLUMN = 2;
	public static final int NOTES_COLUMN = 3;
	public static final int LOCATION_COLUMN = 4;
	public static final int PARENT_LOCAL_COLUMN = 5;
	public static final int PARENT_GLOBAL_COLUMN = 6;
	public static final int DUE_DATE_COLUMN = 7;
	public static final int DUE_TIME_COLUMN = 8;
	public static final int PERCENT_COMPLETE_COLUMN = 9;
	public static final int DONE_COLUMN = 10;
	public static final int PRIORITY_COLUMN = 11;

	private static final String DB_CREATE = "create table " + DB_TABLE + " ("
			+ DB_KEY_ID + " integer primary key autoincrement, "
			+ KEY_GLOBAL_ID + " int, " + KEY_NAME + " text, " + KEY_NOTES
			+ " text, " + KEY_LOCATION + " text, " + KEY_PARENT_LOCAL
			+ " int, " + KEY_PARENT_GLOBAL + " int, " + KEY_DUE_DATE + " int, "
			+ KEY_DUE_TIME + " int, " + KEY_PERCENT_COMPLETE + " int, " + KEY_DONE + " int, "
			+ KEY_PRIORITY + ", int);";

	private SQLiteDatabase db;

	private final Context context;
	private DBHelper dbHelper;

	public static TSDBAdapter getTSDBAdapter(Context _context) {
		
		if(instance == null) {
			instance = new TSDBAdapter(_context);
			return instance;
		} else {
			return instance;
		}
	}
	
	private TSDBAdapter(Context _context) {
		context = _context;
		dbHelper = new DBHelper(context, DB_NAME, null, DB_VERSION);
	}

	public TSDBAdapter open() throws SQLException {
		db = dbHelper.getWritableDatabase();
		return this;
	}

	public void close() {
		db.close();
	}

	public long insertEntry(String name, String notes, String location,
			int parentlocal, int parentglobal, int duedate, int duetime,
			int percentcomplete, int priority) {
		ContentValues contentValues = new ContentValues();
		contentValues.put(KEY_GLOBAL_ID, 0);
		contentValues.put(KEY_NAME, name);
		contentValues.put(KEY_NOTES, notes);
		contentValues.put(KEY_LOCATION, location);
		contentValues.put(KEY_PARENT_LOCAL, parentlocal);
		contentValues.put(KEY_PARENT_GLOBAL, parentglobal);
		contentValues.put(KEY_DUE_DATE, duedate);
		contentValues.put(KEY_DUE_TIME, duetime);
		contentValues.put(KEY_PERCENT_COMPLETE, percentcomplete);
		contentValues.put(KEY_PERCENT_COMPLETE, percentcomplete);
		contentValues.put(KEY_DONE, 0);
		return db.insert(DB_TABLE, null, contentValues);
	}

	public boolean removeEntry(long _rowIndex) {
		return db.delete(DB_TABLE, DB_KEY_ID + "=" + _rowIndex, null) > 0;
	}

	public Cursor getAllEntries() {
		return db.query(DB_TABLE, null, null, null, null, null, null);
	}
	
	public Cursor getTopLevelEntries() {
		return db.query(DB_TABLE, null, KEY_PARENT_LOCAL + "=0", null, null, null, null);
	}
	
	public Cursor getChildrenOf(int local_id) {
		return db.query(DB_TABLE, null, KEY_PARENT_LOCAL + "=" + local_id, null, null, null, null);
	}
	
	public Cursor getTaskWithLocalID(int local_id) {
		return db.query(DB_TABLE, null, DB_KEY_ID + "=" + local_id, null, null, null, null);
	}

	public Cursor getTopLevelEntriesSorted(String sort) {
		if (sort.equals("priority")) {
			return db.query(DB_TABLE, null, KEY_PARENT_LOCAL + "=0", null, null, null,
					KEY_PRIORITY + " DESC");
		}
		if (sort.equals("due"))
		{
			return db.query(DB_TABLE, null, KEY_PARENT_LOCAL + "=0", null, null, null,
					KEY_DUE_DATE + " ASC, " + KEY_DUE_TIME + " ASC");
		}
		return db.query(DB_TABLE, null, KEY_PARENT_LOCAL + "=0", null, null, null, null);
	}

	public int updateEntry(long localID, long globalID, String name,
			String notes, String location, int parentlocal, int parentglobal,
			int duedate, int duetime, int percentcomplete, int priority) {
		String where = DB_KEY_ID + "=" + localID;
		ContentValues contentValues = new ContentValues();
		contentValues.put(KEY_GLOBAL_ID, globalID);
		contentValues.put(KEY_NAME, name);
		contentValues.put(KEY_NOTES, notes);
		contentValues.put(KEY_LOCATION, location);
		contentValues.put(KEY_PARENT_LOCAL, parentlocal);
		contentValues.put(KEY_PARENT_GLOBAL, parentglobal);
		contentValues.put(KEY_DUE_DATE, duedate);
		contentValues.put(KEY_DUE_TIME, duetime);
		contentValues.put(KEY_PERCENT_COMPLETE, percentcomplete);
		contentValues.put(KEY_PRIORITY, priority);
		return db.update(DB_TABLE, contentValues, where, null);
	}
	
	public int changeParent(long localID, int parentLocal, int parentGlobal)
	{
		String where = DB_KEY_ID + "=" + localID;
		ContentValues contentValues = new ContentValues();
		contentValues.put(KEY_PARENT_LOCAL, parentLocal);
		contentValues.put(KEY_PARENT_GLOBAL, parentGlobal);
		return db.update(DB_TABLE, contentValues, where, null);
	}
	
	public int markDone(long localID, int done) {
		String where = DB_KEY_ID + "=" + localID;
		ContentValues contentValues = new ContentValues();
		contentValues.put(KEY_DONE, done);
		return db.update(DB_TABLE, contentValues, where, null);
	}

	private static class DBHelper extends SQLiteOpenHelper {
		public DBHelper(Context context, String name, CursorFactory factory,
				int version) {
			super(context, name, factory, version);
		}

		// Called when no database exists in
		// disk and the helper class needs
		// to create a new one.
		@Override
		public void onCreate(SQLiteDatabase _db) {
			_db.execSQL(DB_CREATE);
		}

		@Override
		public void onUpgrade(SQLiteDatabase _db, int _oldVersion,
				int _newVersion) {
			// Log the version upgrade.
			Log.w("TaskDBAdapter", "Upgrading from version " + _oldVersion
					+ " to " + _newVersion
					+ ", which will destroy all old data");

			_db.execSQL("DROP TABLE IF EXISTS " + DB_TABLE);

			onCreate(_db);
		}
	}
}
